package org.xlin.biz.dao.repast;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.xlin.biz.dao.AbstBaseDao;

/**
 * 
 * 就餐数据处理类
 * @author xl
 *
 */
@Repository
public class RepastDao extends AbstBaseDao<Map<String,Object>>{
	
	/**
	 * 增加就餐记录
	 */
	private final static String AddRecord = " insert into t_mg_repast(tbid) "
			+ " values(:tbid) ";
	
	/**
	 * 判定是否已有未结账餐单
	 */
	private final static String HasNoPayRepast = " select t.id as rid from t_mg_table t,t_mg_repast r "
			+ " where r.tbid=t.id and r.state='0' and t.id=:tbid limit 1 ";
	
	/**
	 * 修改就餐记录(结账使用并且限定修正1次)
	 */
	private final static String ModfiyRecord = " update t_mg_repast t "
			+ " set t.rece=(select IFNULL(sum(u.price*m.num),0) from t_mid_repast_menu m,t_mg_menu u where m.menuid=u.id and m.repastid=t.id), "
			+ " t.actual=:actual, "
			+ " t.descr=:descr, "
			+ " t.state='1', "
			+ " t.end=current_timestamp() "
			+ " where t.id=:id and t.state='0' ";
	
	/**
	 * 删除就餐菜单记录(限定删除未上菜的)
	 */
	private final static String DeleteRecord = " delete from t_mg_repast where id=:id and t.state='0' ";
	
	/**
	 * 增加就餐菜单记录(默认数量1)
	 */
	private final static String AddMenuRecord = " insert into t_mid_repast_menu(repastid,menuid,num) "
			+ " values(:repastid,:menuid,1) ";
	
	/**
	 * 修改就餐菜单记录(只能修正未上餐的记录)
	 */
	private final static String ModfiyMenuRecord = " update t_mid_repast_menu t "
			+ " set t.num=:num "
			+ " where t.id=:id and t.state='0' ";
	
	/**
	 * 删除就餐记录
	 */
	private final static String DeleteMenuRecord = " delete from t_mid_repast_menu where id=:id ";
	
	/**
	 * 获取就餐总金额
	 */
	private final static String GetRepastTotal = " select IFNULL(sum(u.price*m.num),0) from t_mid_repast_menu m,t_mg_menu u where m.menuid=u.id and m.repastid = ? ";
	
	/**
	 * 获取需要处理的菜单
	 */
	private final static String QueryToDoMenus = " select b.num,u.name,m.id,m.num,m.created from t_mg_repast t,t_mg_table b,t_mid_repast_menu m,t_mg_menu u "
			+ " where t.tbid=b.id and t.id=m.repastid and m.menuid=u.id and t.state ='0' and m.state='0' ";
	
	/**
	 * 变更菜单状态为已处理
	 */
	private final static String ModifyMenusDoState = " update t_mid_repast_menu "
			+ " set state = '1' "
			+ " where id = :id ";
	
	
	/**
	 * 分页查询就餐记录列表
	 * @param pageable
	 * @param condition
	 * @return
	 */
	public Page<Map<String, Object>> queryRecord4Page(Pageable pageable, Map<String, Object> condition){
		//查询列表Sql
        StringBuilder querySql = new StringBuilder();
        querySql.append(" select t.* from t_mg_repast t ")
        	.append(" where 1=1 {whereSql} ");
        //查询记录数Sql
        StringBuilder countSql = new StringBuilder();
        countSql.append(" select count(t.id) from t_mg_repast t ")
	    	.append(" where 1=1 {whereSql} ");
        //查询条件Sql
        StringBuilder whereSql = this.getWhereSqlByParseCondition(condition);
        return this.queryPage4Mysql(querySql, countSql, whereSql, pageable, condition, new RowMapper<Map<String, Object>>() {
			@Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> info = new HashMap<String, Object>();
                info.put("id", rs.getObject("id"));
                info.put("rece", rs.getObject("rece"));
                info.put("actual", rs.getObject("actual"));
                info.put("descr", rs.getObject("descr"));
                info.put("state", rs.getObject("state"));
                info.put("created", rs.getObject("created"));
                info.put("end", rs.getObject("end"));
                return info;
            }
        });
	}
	
	/**
     * 条件解析
     * @param condition
     * @return
     */
    public StringBuilder getWhereSqlByParseCondition(Map<String, Object> condition) {
        StringBuilder whereSql = new StringBuilder();
        //暂无条件
        return whereSql;
    }
    
    /**
	 * 增加就餐记录
	 * @param condition
	 * @return
	 */
	public int addRecord(Map<String,Object> condition){
		return this.namesUpdateForMapRetrunId(AddRecord, condition, "id").getKey().intValue();
	}
	
	/**
	 * 判定是否已有未结账餐单
	 * @param condition
	 * @return
	 */
	public boolean hasNoPayRepast(Map<String,Object> condition){
		return !this.getNamedParameterJdbcTemplate().queryForList(HasNoPayRepast, condition).isEmpty();
	}
	
	/**
	 * 更新就餐记录
	 * @param condition
	 * @return
	 */
	public int modfiyRecord(Map<String,Object> condition){
		return this.namesUpdateForMap(ModfiyRecord, condition);
	}
	
	/**
	 * 删除就餐记录
	 * @param condition
	 * @return
	 */
	public void deleteRecord(Map<String,Object> condition){
		this.namesUpdateForMap(DeleteRecord, condition);
	}
	
	/**
	 * 增加就餐菜单记录
	 * @param condition
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public void addMenuRecord(List<Map<String,Object>> params) {
		this.getNamedParameterJdbcTemplate().batchUpdate(AddMenuRecord, params.toArray(new Map[params.size()]));
	}
	
	/**
	 * 更新就餐菜单记录
	 * @param condition
	 * @return
	 */
	public int modfiyMenuRecord(Map<String,Object> condition){
		return this.namesUpdateForMap(ModfiyMenuRecord, condition);
	}
	
	/**
	 * 删除就餐菜单记录
	 * @param condition
	 * @return
	 */
	public void deleteMenuRecord(Map<String,Object> condition){
		this.namesUpdateForMap(DeleteMenuRecord, condition);
	}
	
	/**
	 * 获取就餐总金额
	 * @param repastId
	 * @return
	 */
	public BigDecimal getRepastTotal(int repastId){
		return this.getJdbcTemplate().queryForObject(GetRepastTotal, BigDecimal.class, repastId);
	}
	
	/**
	 * 获取需要处理的菜单
	 * @return
	 */
	public List<Map<String,Object>> queryToDoMenus(){
		return this.getJdbcTemplate().queryForList(QueryToDoMenus);
	}
	
	/**
	 * 变更菜单状态为已处理
	 * @param condition
	 * @return
	 */
	public int modifyMenusDoState(Map<String,Object> condition){
		return this.namesUpdateForMap(ModifyMenusDoState, condition);
	}

}
